import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import random
import matplotlib.pyplot as plt
import seaborn as sns
from matplotlib.gridspec import GridSpec
import matplotlib.ticker as ticker
from matplotlib.font_manager import FontProperties

# 设置中文字体支持
plt.rcParams['font.sans-serif'] = ['SimHei', 'Arial Unicode MS', 'Microsoft YaHei', 'DejaVu Sans']
plt.rcParams['axes.unicode_minus'] = False


# 模拟数据生成函数
def generate_insurance_data():
    # 生成日期范围：2024年全年数据
    dates = pd.date_range(start='2024-01-01', end='2024-12-31')
    regions = ['华东', '华北', '华南', '华中', '西部']
    policy_types = ['车险', '财产险', '责任险', '意外险', '健康险']
    channels = ['直销', '代理', '经纪', '线上', '银保']
    companies = ['公司A', '公司B', '公司C', '公司D', '公司E']

    # 生成再保险合同数据
    reinsurance_contracts = pd.DataFrame({
        'contract_id': [f'RC{str(i).zfill(6)}' for i in range(1, 501)],
        'start_date': random.choices(dates, k=500),
        'end_date': [d + timedelta(days=random.randint(30, 365)) for d in random.choices(dates, k=500)],
        'premium': [round(random.uniform(10000, 500000), 2) for _ in range(500)],
        'is_chief': random.choices([True, False], weights=[0.3, 0.7], k=500),
        'business_type': random.choices(['财险', '寿险'], weights=[0.6, 0.4], k=500),
        'duration_type': random.choices(['长期', '短期'], weights=[0.4, 0.6], k=500),
        'policy_category': random.choices(['团险', '个险'], weights=[0.3, 0.7], k=500),
        'payment_type': random.choices(['期缴', '趸缴'], weights=[0.6, 0.4], k=500),
        'payment_term': random.choices([5, 10, 15, 20, 30], weights=[0.2, 0.3, 0.2, 0.2, 0.1], k=500),
        'is_domestic': random.choices([True, False], weights=[0.8, 0.2], k=500),
        'estimated_premium': [round(random.uniform(8000, 550000), 2) for _ in range(500)]
    })

    # 生成保单数据
    policies = pd.DataFrame({
        'policy_id': [f'PL{str(i).zfill(8)}' for i in range(1, 1001)],
        'issue_date': random.choices(dates, k=1000),
        'policy_type': random.choices(policy_types, k=1000),
        'premium': [round(random.uniform(1000, 50000), 2) for _ in range(1000)],
        'insured_amount': [round(random.uniform(5000, 1000000), 2) for _ in range(1000)],
        'channel': random.choices(channels, k=1000),
        'region': random.choices(regions, k=1000),
        'is_new': random.choices([True, False], weights=[0.3, 0.7], k=1000),
        'duration_type': random.choices(['长期', '短期'], weights=[0.4, 0.6], k=1000),
        'policy_category': random.choices(['团险', '个险'], weights=[0.3, 0.7], k=1000),
        'payment_type': random.choices(['期缴', '趸缴'], weights=[0.6, 0.4], k=1000),
        'payment_term': random.choices([5, 10, 15, 20, 30], weights=[0.2, 0.3, 0.2, 0.2, 0.1], k=1000),
        'is_cancelled': random.choices([True, False], weights=[0.05, 0.95], k=1000),
        'business_value': [round(random.uniform(5000, 100000), 2) for _ in range(1000)]
    })

    # 生成公司资产数据
    assets = pd.DataFrame({
        'date': dates,
        'total_assets': [10000000 + i * 50000 + random.randint(-20000, 20000) for i in range(len(dates))]
    })

    return reinsurance_contracts, policies, assets


# 指标计算函数
def calculate_kpis(reinsurance_contracts, policies, assets, report_date):
    # 转换为日期格式
    if isinstance(report_date, str):
        report_date = datetime.strptime(report_date, '%Y-%m-%d')

    # 时间范围计算
    seven_days_ago = report_date - timedelta(days=7)
    month_start = report_date.replace(day=1)
    year_start = report_date.replace(month=1, day=1)

    # 筛选时间范围内的数据
    def filter_data(df, date_col):
        return df[
            (pd.to_datetime(df[date_col]) >= seven_days_ago) &
            (pd.to_datetime(df[date_col]) <= report_date)
            ]

    reins_7d = filter_data(reinsurance_contracts, 'start_date')
    reins_1m = filter_data(reinsurance_contracts, 'start_date')
    reins_1y = filter_data(reinsurance_contracts, 'start_date')

    policies_7d = filter_data(policies, 'issue_date')
    policies_1m = filter_data(policies, 'issue_date')
    policies_1y = filter_data(policies, 'issue_date')

    # 获取资产数据
    try:
        current_assets = assets[assets['date'] == report_date.strftime('%Y-%m-%d')]['total_assets'].values[0]
        prev_assets = \
            assets[assets['date'] == (report_date - timedelta(days=365)).strftime('%Y-%m-%d')]['total_assets'].values[0]
    except:
        current_assets = 10000000
        prev_assets = 9500000

    # 指标计算函数
    def calculate_metrics(period_reins, period_policies, period_name):
        metrics = []

        # 1. 做首席再保人合同数量占比
        if len(period_reins) > 0:
            chief_ratio = period_reins['is_chief'].mean() * 100
            metrics.append({
                '指标': '做首席再保人合同数量占比',
                '值': round(chief_ratio, 2),
                '周期': period_name,
                '单位': '%'
            })

            # 2. 做首席再保人保费收入占比
            chief_premium = period_reins[period_reins['is_chief']]['premium'].sum()
            total_premium = period_reins['premium'].sum()
            if total_premium > 0:
                premium_ratio = (chief_premium / total_premium) * 100
                metrics.append({
                    '指标': '做首席再保人保费收入占比',
                    '值': round(premium_ratio, 2),
                    '周期': period_name,
                    '单位': '%'
                })

        # 3. 长/短期险保费增长率
        if len(period_policies) > 0:
            # 获取基期数据 (假设基期为去年同期)
            base_date = report_date - timedelta(days=365)
            base_reins = reinsurance_contracts[
                (pd.to_datetime(reinsurance_contracts['start_date']) >= (base_date - timedelta(days=7))) &
                (pd.to_datetime(reinsurance_contracts['start_date']) <= base_date)
                ]

            # 长期险增长率
            current_long_term = period_policies[period_policies['duration_type'] == '长期']['premium'].sum()
            base_long_term = base_reins[base_reins['duration_type'] == '长期']['premium'].sum() if len(
                base_reins) > 0 else current_long_term * 0.9
            long_term_growth = ((
                                        current_long_term - base_long_term) / base_long_term) * 100 if base_long_term > 0 else 0

            # 短期险增长率
            current_short_term = period_policies[period_policies['duration_type'] == '短期']['premium'].sum()
            base_short_term = base_reins[base_reins['duration_type'] == '短期']['premium'].sum() if len(
                base_reins) > 0 else current_short_term * 0.9
            short_term_growth = ((
                                         current_short_term - base_short_term) / base_short_term) * 100 if base_short_term > 0 else 0

            metrics.extend([
                {
                    '指标': '长期险保费增长率',
                    '值': round(long_term_growth, 2),
                    '周期': period_name,
                    '单位': '%'
                },
                {
                    '指标': '短期险保费增长率',
                    '值': round(short_term_growth, 2),
                    '周期': period_name,
                    '单位': '%'
                }
            ])

            # 4. 长/短期险保费占比
            total_premium = period_policies['premium'].sum()
            if total_premium > 0:
                long_term_ratio = (current_long_term / total_premium) * 100
                short_term_ratio = (current_short_term / total_premium) * 100

                metrics.extend([
                    {
                        '指标': '长期险保费占比',
                        '值': round(long_term_ratio, 2),
                        '周期': period_name,
                        '单位': '%'
                    },
                    {
                        '指标': '短期险保费占比',
                        '值': round(short_term_ratio, 2),
                        '周期': period_name,
                        '单位': '%'
                    }
                ])

            # 5. 团/个险保费占比
            group_premium = period_policies[period_policies['policy_category'] == '团险']['premium'].sum()
            individual_premium = period_policies[period_policies['policy_category'] == '个险']['premium'].sum()

            if total_premium > 0:
                group_ratio = (group_premium / total_premium) * 100
                individual_ratio = (individual_premium / total_premium) * 100

                metrics.extend([
                    {
                        '指标': '团险保费占比',
                        '值': round(group_ratio, 2),
                        '周期': period_name,
                        '单位': '%'
                    },
                    {
                        '指标': '个险保费占比',
                        '值': round(individual_ratio, 2),
                        '周期': period_name,
                        '单位': '%'
                    }
                ])

            # 6. 首年期/趸缴保费占比
            new_policies = period_policies[period_policies['is_new']]
            if len(new_policies) > 0:
                installment_premium = new_policies[new_policies['payment_type'] == '期缴']['premium'].sum()
                lump_sum_premium = new_policies[new_policies['payment_type'] == '趸缴']['premium'].sum()
                total_new_premium = installment_premium + lump_sum_premium

                if total_new_premium > 0:
                    installment_ratio = (installment_premium / total_new_premium) * 100
                    lump_sum_ratio = (lump_sum_premium / total_new_premium) * 100

                    metrics.extend([
                        {
                            '指标': '首年期缴保费占比',
                            '值': round(installment_ratio, 2),
                            '周期': period_name,
                            '单位': '%'
                        },
                        {
                            '指标': '首年趸缴保费占比',
                            '值': round(lump_sum_ratio, 2),
                            '周期': period_name,
                            '单位': '%'
                        }
                    ])

            # 7. 10年期及以上期缴保费占比
            if len(new_policies) > 0:
                installment_policies = new_policies[new_policies['payment_type'] == '期缴']
                long_term_installment = installment_policies[installment_policies['payment_term'] >= 10][
                    'premium'].sum()
                total_installment = installment_policies['premium'].sum()

                if total_installment > 0:
                    long_term_ratio = (long_term_installment / total_installment) * 100
                    metrics.append({
                        '指标': '10年期及以上期缴保费占比',
                        '值': round(long_term_ratio, 2),
                        '周期': period_name,
                        '单位': '%'
                    })

            # 8. 犹豫期保费退保率
            new_policies = period_policies[period_policies['is_new']]
            if len(new_policies) > 0:
                cancelled_premium = new_policies[new_policies['is_cancelled']]['premium'].sum()
                total_new_premium = new_policies['premium'].sum()

                if total_new_premium > 0:
                    cancellation_rate = (cancelled_premium / (total_new_premium + cancelled_premium)) * 100
                    metrics.append({
                        '指标': '犹豫期保费退保率',
                        '值': round(cancellation_rate, 2),
                        '周期': period_name,
                        '单位': '%'
                    })

            # 9. 新单业务价值占比
            if len(period_policies) > 0:
                new_business_value = period_policies[period_policies['is_new']]['business_value'].sum()
                total_business_value = period_policies['business_value'].sum()

                if total_business_value > 0:
                    value_ratio = (new_business_value / total_business_value) * 100
                    metrics.append({
                        '指标': '新单业务价值占比',
                        '值': round(value_ratio, 2),
                        '周期': period_name,
                        '单位': '%'
                    })

            # 10. 资产增量保费比
            insurance_income = period_policies['premium'].sum()
            if insurance_income > 0:
                asset_growth_ratio = ((current_assets - prev_assets) / insurance_income) * 100
                metrics.append({
                    '指标': '资产增量保费比',
                    '值': round(asset_growth_ratio, 2),
                    '周期': period_name,
                    '单位': '%'
                })

            # 11. 保费预估差异率
            if len(period_reins) > 0:
                estimated_premium = period_reins['estimated_premium'].sum()
                actual_premium = period_reins['premium'].sum()

                if actual_premium > 0:
                    diff_ratio = ((estimated_premium - actual_premium) / actual_premium) * 100
                    metrics.append({
                        '指标': '保费预估差异率',
                        '值': round(diff_ratio, 2),
                        '周期': period_name,
                        '单位': '%'
                    })

        return metrics

    # 计算各时间周期的指标
    results = []

    # 7日指标
    results.extend(calculate_metrics(reins_7d, policies_7d, '7日'))

    # 月度指标
    results.extend(calculate_metrics(reins_1m, policies_1m, '月度'))

    # 年度指标
    results.extend(calculate_metrics(reins_1y, policies_1y, '年度'))

    return pd.DataFrame(results)


# 可视化函数
def visualize_kpis(kpi_results, report_date):
    """生成KPI可视化图表"""
    print("生成可视化图表...")

    # 创建图表文件夹
    import os
    folder_name = f"保险KPI可视化_{report_date}"
    os.makedirs(folder_name, exist_ok=True)

    # 设置颜色主题
    period_colors = {
        '7日': '#3498db',
        '月度': '#2ecc71',
        '年度': '#e74c3c'
    }

    # 1. 关键指标对比图
    key_metrics = [
        '做首席再保人合同数量占比',
        '长期险保费增长率',
        '团险保费占比',
        '首年期缴保费占比',
        '10年期及以上期缴保费占比',
        '新单业务价值占比'
    ]

    plt.figure(figsize=(16, 12))
    plt.suptitle(f'关键保险指标对比 - {report_date}', fontsize=16)

    for i, metric in enumerate(key_metrics, 1):
        plt.subplot(2, 3, i)
        metric_data = kpi_results[kpi_results['指标'] == metric]

        if not metric_data.empty:
            sns.barplot(data=metric_data, x='周期', y='值', hue='周期', palette=period_colors,
                        dodge=False, legend=False)
            plt.title(metric)
            plt.ylabel('值 (%)' if metric_data['单位'].iloc[0] == '%' else '值')
            plt.grid(axis='y', linestyle='--', alpha=0.7)

            # 在柱子上方添加数值标签
            for index, row in metric_data.iterrows():
                plt.text(row.name % 3, row['值'] + 0.5, f"{row['值']}%",
                         ha='center', fontsize=10)

    plt.tight_layout(rect=[0, 0, 1, 0.96])
    plt.savefig(f"{folder_name}/1_关键指标对比.png", dpi=300)

    # 2. 保费结构分析
    plt.figure(figsize=(16, 10))
    gs = GridSpec(2, 2, figure=plt.gcf())
    plt.suptitle(f'保费结构分析 - {report_date}', fontsize=16)

    # 长短期险占比
    ax1 = plt.subplot(gs[0, 0])
    long_term = kpi_results[(kpi_results['指标'] == '长期险保费占比') & (kpi_results['周期'] == '年度')]['值'].values
    short_term = kpi_results[(kpi_results['指标'] == '短期险保费占比') & (kpi_results['周期'] == '年度')]['值'].values

    if len(long_term) > 0 and len(short_term) > 0:
        labels = ['长期险', '短期险']
        sizes = [long_term[0], short_term[0]]
        colors = ['#3498db', '#e74c3c']
        explode = (0.1, 0)

        ax1.pie(sizes, explode=explode, labels=labels, colors=colors, autopct='%1.1f%%',
                shadow=True, startangle=90)
        ax1.axis('equal')
        ax1.set_title('长短期险保费占比(年度)')

    # 团个险占比
    ax2 = plt.subplot(gs[0, 1])
    group = kpi_results[(kpi_results['指标'] == '团险保费占比') & (kpi_results['周期'] == '年度')]['值'].values
    individual = kpi_results[(kpi_results['指标'] == '个险保费占比') & (kpi_results['周期'] == '年度')]['值'].values

    if len(group) > 0 and len(individual) > 0:
        labels = ['团险', '个险']
        sizes = [group[0], individual[0]]
        colors = ['#2ecc71', '#9b59b6']
        explode = (0.1, 0)

        ax2.pie(sizes, explode=explode, labels=labels, colors=colors, autopct='%1.1f%%',
                shadow=True, startangle=90)
        ax2.axis('equal')
        ax2.set_title('团险个险保费占比(年度)')

    # 期缴趸缴占比
    ax3 = plt.subplot(gs[1, 0])
    installment = kpi_results[(kpi_results['指标'] == '首年期缴保费占比') & (kpi_results['周期'] == '年度')][
        '值'].values
    lump_sum = kpi_results[(kpi_results['指标'] == '首年趸缴保费占比') & (kpi_results['周期'] == '年度')]['值'].values

    if len(installment) > 0 and len(lump_sum) > 0:
        labels = ['期缴', '趸缴']
        sizes = [installment[0], lump_sum[0]]
        colors = ['#f39c12', '#1abc9c']
        explode = (0.1, 0)

        ax3.pie(sizes, explode=explode, labels=labels, colors=colors, autopct='%1.1f%%',
                shadow=True, startangle=90)
        ax3.axis('equal')
        ax3.set_title('首年期缴趸缴保费占比(年度)')

    # 10年期及以上期缴占比
    ax4 = plt.subplot(gs[1, 1])
    long_term_installment = \
    kpi_results[(kpi_results['指标'] == '10年期及以上期缴保费占比') & (kpi_results['周期'] == '年度')]['值'].values

    if len(long_term_installment) > 0:
        labels = ['10年+期缴', '其他期缴']
        sizes = [long_term_installment[0], 100 - long_term_installment[0]]
        colors = ['#d35400', '#7f8c8d']
        explode = (0.1, 0)

        ax4.pie(sizes, explode=explode, labels=labels, colors=colors, autopct='%1.1f%%',
                shadow=True, startangle=90)
        ax4.axis('equal')
        ax4.set_title('10年期及以上期缴保费占比(年度)')

    plt.tight_layout(rect=[0, 0, 1, 0.96])
    plt.savefig(f"{folder_name}/2_保费结构分析.png", dpi=300)

    # 3. 增长率指标趋势
    growth_metrics = [
        '长期险保费增长率',
        '短期险保费增长率',
        '新单业务价值占比'
    ]

    plt.figure(figsize=(14, 8))
    plt.suptitle(f'增长率指标趋势 - {report_date}', fontsize=16)

    for i, metric in enumerate(growth_metrics, 1):
        plt.subplot(1, 3, i)
        metric_data = kpi_results[kpi_results['指标'] == metric]

        if not metric_data.empty:
            # 按周期顺序排序
            period_order = ['7日', '月度', '年度']
            metric_data['周期'] = pd.Categorical(metric_data['周期'], categories=period_order, ordered=True)
            metric_data = metric_data.sort_values('周期')

            # 绘制折线图
            plt.plot(metric_data['周期'], metric_data['值'], marker='o', linestyle='-',
                     color=period_colors[metric_data['周期'].iloc[0]], markersize=8, linewidth=2)

            plt.title(metric)
            plt.ylabel('增长率 (%)')
            plt.grid(True, linestyle='--', alpha=0.6)

            # 添加数据标签
            for _, row in metric_data.iterrows():
                plt.text(row['周期'], row['值'] + 1, f"{row['值']}%", ha='center', fontsize=10)

    plt.tight_layout(rect=[0, 0, 1, 0.96])
    plt.savefig(f"{folder_name}/3_增长率趋势.png", dpi=300)

    # 4. 首席再保人分析
    chief_metrics = [
        '做首席再保人合同数量占比',
        '做首席再保人保费收入占比'
    ]

    plt.figure(figsize=(12, 6))
    plt.suptitle(f'首席再保人表现 - {report_date}', fontsize=16)

    for i, metric in enumerate(chief_metrics, 1):
        plt.subplot(1, 2, i)
        metric_data = kpi_results[kpi_results['指标'] == metric]

        if not metric_data.empty:
            # 按周期顺序排序
            period_order = ['7日', '月度', '年度']
            metric_data['周期'] = pd.Categorical(metric_data['周期'], categories=period_order, ordered=True)
            metric_data = metric_data.sort_values('周期')

            # 绘制柱状图
            bars = plt.bar(metric_data['周期'], metric_data['值'],
                           color=[period_colors[p] for p in metric_data['周期']])
            plt.title(metric)
            plt.ylabel('占比 (%)')
            plt.grid(axis='y', linestyle='--', alpha=0.7)

            # 在柱子上方添加数值标签
            for bar in bars:
                height = bar.get_height()
                plt.text(bar.get_x() + bar.get_width() / 2., height + 1,
                         f'{height}%', ha='center', va='bottom')

    plt.tight_layout(rect=[0, 0, 1, 0.96])
    plt.savefig(f"{folder_name}/4_首席再保人分析.png", dpi=300)

    # 5. 风险与效率指标
    risk_metrics = [
        '犹豫期保费退保率',
        '保费预估差异率',
        '资产增量保费比'
    ]

    plt.figure(figsize=(14, 6))
    plt.suptitle(f'风险与效率指标 - {report_date}', fontsize=16)

    for i, metric in enumerate(risk_metrics, 1):
        plt.subplot(1, 3, i)
        metric_data = kpi_results[kpi_results['指标'] == metric]

        if not metric_data.empty:
            # 按周期顺序排序
            period_order = ['7日', '月度', '年度']
            metric_data['周期'] = pd.Categorical(metric_data['周期'], categories=period_order, ordered=True)
            metric_data = metric_data.sort_values('周期')

            # 绘制柱状图
            bars = plt.bar(metric_data['周期'], metric_data['值'],
                           color=[period_colors[p] for p in metric_data['周期']])
            plt.title(metric)
            plt.ylabel('值 (%)' if metric_data['单位'].iloc[0] == '%' else '值')
            plt.grid(axis='y', linestyle='--', alpha=0.7)

            # 在柱子上方添加数值标签
            for bar in bars:
                height = bar.get_height()
                plt.text(bar.get_x() + bar.get_width() / 2., height + (0.05 * max(metric_data['值'])),
                         f'{height}%', ha='center', va='bottom')

    plt.tight_layout(rect=[0, 0, 1, 0.96])
    plt.savefig(f"{folder_name}/5_风险与效率指标.png", dpi=300)

    print(f"可视化图表已保存至 {folder_name} 文件夹")


# 主程序
def main():
    # 设置报告日期
    report_date = '2024-12-31'

    # 生成模拟数据
    print("正在生成模拟数据...")
    reinsurance_contracts, policies, assets = generate_insurance_data()

    # 计算业务指标
    print("正在计算业务指标...")
    kpi_results = calculate_kpis(reinsurance_contracts, policies, assets, report_date)

    # 导出到Excel
    output_file = f"八维保险数据挖掘_{report_date}.xlsx"
    kpi_results.to_excel(output_file, index=False)
    print(f"数据已导出至: {output_file}")

    # 显示部分结果
    print("\n指标概览:")
    print(kpi_results.head(15))

    # 生成可视化图表
    visualize_kpis(kpi_results, report_date)


if __name__ == "__main__":
    main()